﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using exel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using System.Globalization;
using VietKinsNutrition.Common;

namespace VietKinsNutrition.Bussiness
{
    public class ClsExel
    {
        #region Định nghĩa thuộc tính

        public static exel.Application _exlApplication;
        public static exel.Workbook _xlWorkBook;
        public static exel.Worksheet _xlWorkSheet;
        public static exel.Range rang;
        public static exel.Range _rang;
        public int _Start;
        public static object Value = System.Reflection.Missing.Value;

        #endregion

        public void OpenFileExel(string FileName)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
                oExcel.Visible = true;
                oExcel.UserControl = true;
                Microsoft.Office.Interop.Excel.Workbooks oBooks = oExcel.Workbooks;
                object oOpt = System.Reflection.Missing.Value; //for optional arguments
                oBooks.Open(FileName, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt);
            }
            catch
            {
            }
        }
        public virtual void FileEX()
        {
            int rowIndex = 1;

            _exlApplication = new exel.ApplicationClass();
            _exlApplication.Visible = true;
            _xlWorkBook = _exlApplication.Workbooks.Add(Value);
            _xlWorkSheet = (exel.Worksheet)_xlWorkBook.Worksheets[1];
            _xlWorkSheet.Activate();
        }

        public virtual void NCDDbyWeek(int SHD, string Tieude, string Tenso, string Tenphong, string Tentruong, DataGridView Grid, string FileName, DateTime dateReport)
        {
            string[] Chuoi = new string[702];
            int g = 0;
            string[] _Temp = dateReport.ToString("dd/MM/yyyy").Split('/');
            // string _Value = _Temp[0] + "/" + _Temp[1] + "/" + _Temp[2].Remove(4);
            // MessageBox.Show(_Temp[0] + "/" + _Temp[1] + "/" + _Temp[2].Remove(4) + "=" + _Value);
            string[] ch = new string[676];
            int temp = 0, jCel = 0, lenColumn = Grid.ColumnCount;
            for (int i = 65; i < 91; i++)
            {
                Chuoi[g] = (Convert.ToChar(i).ToString());
                g = g + 1;
            }
            for (int i = 65; i < 91; i++)
            {
                for (int j = 65; j < 91; j++)
                {
                    Chuoi[g] = (Convert.ToChar(i).ToString() + Convert.ToChar(j).ToString());
                    g = g + 1;
                }
            }
            _exlApplication = new exel.ApplicationClass();

            _exlApplication.Visible = true;
            _xlWorkBook = _exlApplication.Workbooks.Add(Value);
            _xlWorkSheet = (exel.Worksheet)_xlWorkBook.Worksheets.get_Item(1);

            try
            {
                _xlWorkSheet.Cells[1, 1] = Tenso;
                _xlWorkSheet.Cells[2, 1] = Tenphong;
                _xlWorkSheet.Cells[3, 1] = Tentruong;

                string colStart = "A";
                string colEnd = ExcelFunc.NextChar(colStart, Grid.Columns.GetColumnCount(DataGridViewElementStates.Visible) - 1);

                _rang = _xlWorkSheet.get_Range(colStart + "4", colEnd + "4");
                _rang.Merge(false);
                _rang.Font.Size = "18";
                _rang.Value2 = Tieude;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range(colStart + "5", colEnd + "5");
                _rang.Merge(false);
                _rang.Value2 = "'" + dateReport;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;


                for (int j = 0; j < Grid.ColumnCount; j++)
                {
                    // in tiêu đề
                    if (Grid.Columns[j].Visible == true)
                    {
                        jCel = jCel + 1;
                        _xlWorkSheet.Cells[_Start, jCel] = "'" + Grid.Columns[j].HeaderText;

                        for (int i = 0; i < Grid.Rows.Count; i++)
                        {
                            if (jCel < 3)
                            {
                                if (jCel == 1)
                                {
                                    _rang = _xlWorkSheet.get_Range(Chuoi[jCel - 1] + (i + _Start).ToString(), Chuoi[jCel - 1] + (i + _Start + 1).ToString());

                                    _rang.HorizontalAlignment = 3;
                                    _rang.ColumnWidth = 10;
                                    DataGridViewCell Cell = Grid[j, i];
                                    _xlWorkSheet.Cells[i + _Start + 1, jCel] = Cell.Value;
                                }
                                else
                                {
                                    _rang = _xlWorkSheet.get_Range(Chuoi[jCel - 1] + (i + _Start).ToString(), Chuoi[jCel - 1] + (i + _Start + 1).ToString());
                                    _rang.HorizontalAlignment = 2;
                                    _rang.ColumnWidth = 25;
                                    DataGridViewCell Cell = Grid[j, i];
                                    _xlWorkSheet.Cells[i + _Start + 1, jCel] = Cell.Value;
                                    temp = i;
                                }
                            }
                            // in gía trị

                            else
                            {
                                _rang = _xlWorkSheet.get_Range(Chuoi[jCel - 1] + (i + _Start).ToString(), Chuoi[jCel - 1] + (i + _Start + 1).ToString());
                                _rang.ColumnWidth = 15;
                                DataGridViewCell Cell = Grid[j, i];
                                _xlWorkSheet.Cells[i + _Start + 1, jCel] = Cell.Value;
                                temp = i;
                            }
                        }
                    }
                }
                _rang = _xlWorkSheet.get_Range("a11", "a12");
                _rang.Merge(false);
                _rang.ColumnWidth = 6;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("b11", "b12");
                _rang.Merge(false); _rang.ColumnWidth = 20;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("c11", "c12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 10;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("d11", "d12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 10;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("e11", "e12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 10;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("f11", "f12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 10;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("g11", "g12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 10;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("h11", "h12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 10;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("i11", "i12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 10;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("d11", "o12");
                _rang.ColumnWidth = 9;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("j11", "j12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 9;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("k11", "k12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 9;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("p11", "p12");
                _rang.Merge(false);
                _rang.WrapText = true;
                _rang.ColumnWidth = 9;
                _rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                _rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                _rang = _xlWorkSheet.get_Range("a11", "p12");
                _rang.Font.Bold = true;
                _rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.Turquoise);
                rang = _xlWorkSheet.get_Range("a11", "p" + (11 + Grid.Rows.Count + 1));
                rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlContinuous;

            }
            catch (System.Exception ex)
            { MessageBox.Show("loi export file exle " + ex.Message); }

        }

        public virtual void ExportHoadondicho(int SHD, string Tieude, string Tenso, string Tenphong, string Tentruong, DataGridView Grid, string FileName, DateTime dateReport, string sChiphiphatsinh, string chi_nt, string chi_mg, int iTongsotre_nt, int iTongsotre_mg)
        {


            int rowIndex = 3;

            _exlApplication = new exel.ApplicationClass();
            _exlApplication.Visible = true;
            _xlWorkBook = _exlApplication.Workbooks.Add(Value);
            _xlWorkSheet = (exel.Worksheet)_xlWorkBook.Worksheets[1];
            _xlWorkSheet.Cells.Font.Name = "Arial";
            _xlWorkSheet.Activate();

            rang = _xlWorkSheet.get_Range("a1", "l2");
            rang.Select();
            rang.Merge(false);
            rang.Font.Bold = true;
            rang.Font.Size = 16;
            rang.Value2 = "HÓA ĐƠN ĐI CHỢ";
            rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
            rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
            NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
            nfi.NumberGroupSeparator = ".";
            nfi.NumberDecimalSeparator = ",";
            try
            {
                // Căn chỉnh kiểu hiển thị và độ rộng cột
                #region "Can chinh kieu hien thi va do rong cot"
                rowIndex += 1;
                rang = (exel.Range)_xlWorkSheet.Columns["a", System.Type.Missing];
                rang.ColumnWidth = 20;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["b", System.Type.Missing];
                rang.ColumnWidth = 8;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["c", System.Type.Missing];
                rang.ColumnWidth = 7;
                //rang.NumberFormat = "#,##0.0";

                rang = (exel.Range)_xlWorkSheet.Columns["d", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.NumberFormat = "#,###";
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                rang = (exel.Range)_xlWorkSheet.Columns["e", System.Type.Missing];
                rang.ColumnWidth = 7;
                rang.NumberFormat = "#,##0.0";
                rang = (exel.Range)_xlWorkSheet.Columns["f", System.Type.Missing];
                rang.ColumnWidth = 10;
                rang.NumberFormat = "#,###";
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                rang = (exel.Range)_xlWorkSheet.Columns["g", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.NumberFormat = "#,###";
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                rang = (exel.Range)_xlWorkSheet.Columns["h", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.NumberFormat = "#,##0.0";

                rang = (exel.Range)_xlWorkSheet.Columns["i", System.Type.Missing];
                rang.ColumnWidth = 10;
                rang.NumberFormat = "#,###";
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                rang = (exel.Range)_xlWorkSheet.Columns["j", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.NumberFormat = "#,###";
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                rang = (exel.Range)_xlWorkSheet.Columns["k", System.Type.Missing];
                rang.ColumnWidth = 7;
                //rang.NumberFormat = "#,##0.0";

                rang = (exel.Range)_xlWorkSheet.Columns["l", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;
                rang.NumberFormat = "#,###";
                #endregion


                //export to rowIndex = 1 => xuất phát từ dòng đầu tiên

                //System.Data.DataTable dtTongbaoan = ClsTblBaoanBLL.GetSumAllbyDay(dateReport);

                #region "Xuat dong tong ket - Thu/ngay/thang - Soluong"
                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Select();
                switch (dateReport.DayOfWeek)
                {
                    case DayOfWeek.Monday:
                        rang.Value2 = "Thứ 2"; break;
                    case DayOfWeek.Tuesday:
                        rang.Value2 = "Thứ 3"; break;
                    case DayOfWeek.Wednesday:
                        rang.Value2 = "Thứ 4"; break;
                    case DayOfWeek.Thursday:
                        rang.Value2 = "Thứ 5"; break;
                    case DayOfWeek.Friday:
                        rang.Value2 = "Thứ 6"; break;
                    case DayOfWeek.Saturday:
                        rang.Value2 = "Thứ 7"; break;
                    case DayOfWeek.Sunday:
                        rang.Value2 = "Chủ nhật"; break;
                }

                rang = _xlWorkSheet.get_Range("b" + rowIndex, "d" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Ngày " + dateReport.Day + " Tháng " + dateReport.Month + " Năm " + dateReport.Year;

                rang = _xlWorkSheet.get_Range("e" + rowIndex, "f" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tổng số trẻ:";


                rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                rang.Value2 = "Nhà trẻ:";


                rang = _xlWorkSheet.get_Range("j" + rowIndex, System.Type.Missing);
                rang.Value2 = "Mẫu giáo:";

                //if (dtTongbaoan != null && dtTongbaoan.Rows.Count > 0)
                //{
                //    rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                //    rang.Value2 = dtTongbaoan.Rows[0]["Tongsosuat"].ToString();

                //    rang = _xlWorkSheet.get_Range("i" + rowIndex, System.Type.Missing);
                //    rang.Value2 = dtTongbaoan.Rows[0]["Nhatre"].ToString();

                //    rang = _xlWorkSheet.get_Range("k" + rowIndex, "l" + rowIndex);
                //    rang.Select();
                //    rang.Merge(false);
                //    rang.Value2 = dtTongbaoan.Rows[0]["Maugiao"].ToString();
                //}
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                rang.Font.Bold = true;

                rang = _xlWorkSheet.get_Range("a2", "l3");
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rowIndex += 1;
                #endregion

                #region "Xuat tieu de"
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "a" + (rowIndex + 1));
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tên thực phẩm";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("b" + rowIndex, "b" + (rowIndex + 1));
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "ĐVT";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("c" + rowIndex, "d" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tồn hôm trước";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("e" + rowIndex, "g" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Nhập trong ngày";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("h" + rowIndex, "j" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Xuất trong ngày";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("k" + rowIndex, "l" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tồn cuối ngày";
                rang.Font.Bold = true;
                rang.RowHeight = 25;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rowIndex += 1;

                rang = _xlWorkSheet.get_Range("c" + rowIndex, System.Type.Missing);
                rang.Value2 = "SL";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);

                rang.Value2 = "Thành tiền";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("e" + rowIndex, System.Type.Missing);
                rang.Value2 = "SL";

                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("f" + rowIndex, System.Type.Missing);
                rang.Value2 = "Đơn giá";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Thành tiền";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                rang.Value2 = "SL";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("i" + rowIndex, System.Type.Missing);
                rang.Value2 = "Đơn giá";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("j" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thành tiền";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("k" + rowIndex, System.Type.Missing);
                rang.Value2 = "SL"; rang.Font.Bold = true;

                rang = _xlWorkSheet.get_Range("l" + rowIndex, System.Type.Missing);
                rang.Select();
                rang.Merge(false); rang.Font.Bold = true;
                rang.Value2 = "Thành tiền";
                rang.RowHeight = 21;
                rang = _xlWorkSheet.get_Range("a" + (rowIndex - 1), "l" + (rowIndex));
                rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.Turquoise);

                #endregion

                #region "Xuat thuc pham cũ"
                //rowIndex += 1;
                //int startIndex = rowIndex;
                //for (int i = 0; i < Grid.Rows.Count; i++)
                //{
                //    rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                //    rang.Value2 = Grid["Tenthucan", i].Value;
                //    rang.RowHeight = 17.25;

                //    rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                //    rang.Value2 = Grid["Tendonvitinh", i].Value;

                //    rang = _xlWorkSheet.get_Range("c" + rowIndex, System.Type.Missing);
                //    rang.Value2 = Grid["SLTon", i].Value;
                //    rang = _xlWorkSheet.get_Range("e" + rowIndex, System.Type.Missing);
                //    rang.Value2 = Grid["SLNhap", i].Value;
                //    rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                //    rang.Value2 = Grid["SLXuat", i].Value;
                //    rang = _xlWorkSheet.get_Range("k" + rowIndex, System.Type.Missing);
                //    rang.Value2 = "=c" + rowIndex + "+e" + rowIndex + "-h" + rowIndex;
                //        rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                //    if (Grid["GiaTon", i].Value.ToString().Trim() != "")
                //        rang.Value2 = "=" + Grid["GiaTon", i].Value.ToString() + "*c" + rowIndex;
                //    else
                //        rang.Value2 = "=0*c" + rowIndex;
                //    rang = _xlWorkSheet.get_Range("f" + rowIndex, System.Type.Missing);
                //    rang.Value2 = Grid["GiaNhap", i].Value;
                //    rang = _xlWorkSheet.get_Range("i" + rowIndex, System.Type.Missing);
                //    rang.Value2 = Grid["GiaXuat", i].Value;
                //    rang = _xlWorkSheet.get_Range("l" + rowIndex, System.Type.Missing);
                //    rang.Value2 = "=if(f" + rowIndex + ">0,f" + rowIndex + "*k" + rowIndex + ", if(d" + rowIndex + ">0, d" + rowIndex + "*k" + rowIndex + "/c" + rowIndex + ",0))";

                //    rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                //    rang.Value2 = "=e" + rowIndex.ToString() + "*f" + rowIndex.ToString();
                //    rang = _xlWorkSheet.get_Range("j" + rowIndex, System.Type.Missing);
                //    rang.Value2 = "=h" + rowIndex.ToString() + "*i" + rowIndex.ToString();

                //    rowIndex += 1;
                //}
                ////Đóng khung bảng thực phẩm
                //rang = _xlWorkSheet.get_Range("a" + (startIndex - 2), "l" + (rowIndex - 1));
                //rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                //rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                //rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlContinuous;

                ////Dòng tổng kết của bảng thực phẩm
                //rowIndex += 1;
                //rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                //rang.Select();
                //rang.Value2 = "Tổng";
                //rang.RowHeight = 17.5;

                //rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                //rang.Select();
                //rang.Merge(false);
                //rang.Value2 = "=sum(d" + startIndex.ToString() + ":d" + (rowIndex - 2).ToString() + ")";

                //rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                //rang.Select();
                //rang.Merge(false);
                //rang.Value2 = "=sum(g" + startIndex.ToString() + ":g" + (rowIndex - 2).ToString() + ")";

                //rang = _xlWorkSheet.get_Range("j" + rowIndex, System.Type.Missing);
                //rang.Select();
                //rang.Merge(false);
                //rang.Value2 = "=sum(j" + startIndex.ToString() + ":j" + (rowIndex - 2).ToString() + ")";

                //rang = _xlWorkSheet.get_Range("l" + rowIndex, System.Type.Missing);
                //rang.Select();
                //rang.Merge(false);
                //rang.Value2 = "=sum(l" + startIndex.ToString() + ":l" + (rowIndex - 2).ToString() + ")";
                //rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                //rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.PaleGoldenrod);
                //rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                //rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                //rang.Font.Bold = true;



                //rowIndex += 1;
                #endregion

                #region "Xuat thuc pham mới"
                rowIndex += 1;
                int startIndex = rowIndex;
                double truoc = 0;
                double nhap = 0;
                double xuat = 0;
                double cuoingay = 0;
                for (int i = 0; i < Grid.Rows.Count; i++)
                {
                    rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                    rang.Value2 = Grid["Tenthucan", i].Value;
                    rang.RowHeight = 17.25;
                    rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                    rang.Value2 = Grid["Tendonvitinh", i].Value;

                    rang = _xlWorkSheet.get_Range("c" + rowIndex, System.Type.Missing);
                    int s; string tg = ""; string test = "0"; double s1;
                    if (Grid["SLTon", i].Value.ToString() != "")
                        test = Grid["SLTon", i].Value.ToString();
                    s1 = double.Parse(test);
                    tg = s1.ToString("N1", nfi);
                    rang.Value2 = tg;

                    rang = _xlWorkSheet.get_Range("e" + rowIndex, System.Type.Missing);
                    tg = ""; test = "0"; s1 = 0;
                    if (Grid["SLNhap", i].Value.ToString() != "")
                        test = Grid["SLNhap", i].Value.ToString();
                    s1 = double.Parse(test);
                    tg = s1.ToString("N1", nfi);
                    rang.Value2 = tg;
                    //        rang.Value2 = "'" + Grid["SLNhap", i].Value;


                    rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                    tg = ""; test = "0"; s1 = 0;
                    if (Grid["SLXuat", i].Value.ToString() != "")
                        test = Grid["SLXuat", i].Value.ToString();
                    s1 = double.Parse(test);
                    tg = s1.ToString("N1", nfi);
                    rang.Value2 = tg;
                    //      rang.Value2 = "'" + Grid["SLXuat", i].Value;

                    rang = _xlWorkSheet.get_Range("k" + rowIndex, System.Type.Missing);
                    if (Grid["SLTon", i].Value.ToString() == "")
                        Grid["SLTon", i].Value = 0;
                    if (Grid["SLNhap", i].Value.ToString() == "")
                        Grid["SLNhap", i].Value = 0;
                    if (Grid["SLXuat", i].Value.ToString() == "")
                        Grid["SLXuat", i].Value = 0;
                    if (Grid["GiaXuat", i].Value.ToString() == "")
                        Grid["GiaXuat", i].Value = 0;
                    //     rang.Value2 = "'" + (double.Parse(Grid["SLTon", i].Value.ToString()) + double.Parse(Grid["SLNhap", i].Value.ToString()) - double.Parse(Grid["SLXuat", i].Value.ToString())).ToString();
                    rang.Value2 = (double.Parse(Grid["SLTon", i].Value.ToString()) + double.Parse(Grid["SLNhap", i].Value.ToString()) - double.Parse(Grid["SLXuat", i].Value.ToString())).ToString("N1", nfi);



                    // rang.Value2 = "=c" + rowIndex + "+e" + rowIndex + "-h" + rowIndex;
                    rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                    if (Grid["GiaTon", i].Value.ToString().Trim() != "")
                    // rang.Value2 = "=" + Grid["GiaTon", i].Value.ToString() + "*c" + rowIndex;
                    {
                        rang.Value2 = "'" + (double.Parse(Grid["GiaTon", i].Value.ToString()) * double.Parse(Grid["SLTon", i].Value.ToString())).ToString("N0", nfi);
                        truoc = truoc + double.Parse((double.Parse(Grid["GiaTon", i].Value.ToString()) * double.Parse(Grid["SLTon", i].Value.ToString())).ToString());
                    }
                    else
                        // rang.Value2 = "=0*c" + rowIndex;
                        rang.Value2 = 0;
                    rang = _xlWorkSheet.get_Range("f" + rowIndex, System.Type.Missing);
                    tg = ""; test = "0"; s1 = 0; s = 0;
                    if (Grid["GiaNhap", i].Value.ToString() != "")
                        test = Grid["GiaNhap", i].Value.ToString();
                    s1 = double.Parse(test);
                    tg = s1.ToString("N0", nfi);
                    rang.Value2 = "'" + tg;
                    // rang.Value2 = "'" + Grid["GiaNhap", i].Value;


                    rang = _xlWorkSheet.get_Range("i" + rowIndex, System.Type.Missing);
                    tg = ""; test = "0"; s1 = 0; s = 0;
                    if (Grid["GiaXuat", i].Value.ToString() != "")
                        test = Grid["GiaXuat", i].Value.ToString();
                    s1 = double.Parse(test);
                    tg = s1.ToString("N0", nfi);
                    rang.Value2 = "'" + tg;
                    //    rang.Value2 = "'" + Grid["GiaXuat", i].Value;

                    _rang = _xlWorkSheet.get_Range("l" + rowIndex, System.Type.Missing);

                    if (Grid["GiaNhap", i].Value.ToString().Trim() != "" && Grid["GiaNhap", i].Value.ToString().Trim() != "0")
                    {
                        _rang.Value2 = "'" + (double.Parse(Grid["GiaNhap", i].Value.ToString()) * (double.Parse(Grid["SLTon", i].Value.ToString()) + double.Parse(Grid["SLNhap", i].Value.ToString()) - double.Parse(Grid["SLXuat", i].Value.ToString()))).ToString("N0", nfi);
                        cuoingay += double.Parse(Grid["GiaNhap", i].Value.ToString()) * (double.Parse(Grid["SLTon", i].Value.ToString()) + double.Parse(Grid["SLNhap", i].Value.ToString()) - double.Parse(Grid["SLXuat", i].Value.ToString()));
                    }
                    else
                    {
                        if (Grid["GiaTon", i].Value.ToString().Trim() != "")
                        {
                            _rang.Value2 = "'" + (double.Parse(Grid["GiaTon", i].Value.ToString()) * (double.Parse(Grid["SLTon", i].Value.ToString()) + double.Parse(Grid["SLNhap", i].Value.ToString()) - double.Parse(Grid["SLXuat", i].Value.ToString()))).ToString("N0", nfi);
                            cuoingay += double.Parse(Grid["GiaTon", i].Value.ToString()) * (double.Parse(Grid["SLTon", i].Value.ToString()) + double.Parse(Grid["SLNhap", i].Value.ToString()) - double.Parse(Grid["SLXuat", i].Value.ToString()));
                        }
                        else
                            _rang.Value2 = 0;
                    }


                    // rang.Value2 = "=if(f" + rowIndex + ">0,f" + rowIndex + "*k" + rowIndex + ", if(d" + rowIndex + ">0, d" + rowIndex + "*k" + rowIndex + "/c" + rowIndex + ",0))";

                    rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                    rang.Value2 = "'" + (double.Parse(Grid["GiaNhap", i].Value.ToString()) * double.Parse(Grid["SLNhap", i].Value.ToString())).ToString("N0", nfi);
                    nhap += double.Parse(Grid["GiaNhap", i].Value.ToString()) * double.Parse(Grid["SLNhap", i].Value.ToString());
                    //  rang.Value2 = "=e" + rowIndex.ToString() + "*f" + rowIndex.ToString();
                    rang = _xlWorkSheet.get_Range("j" + rowIndex, System.Type.Missing);
                    rang.Value2 = "'" + (double.Parse(Grid["GiaXuat", i].Value.ToString()) * double.Parse(Grid["SLXuat", i].Value.ToString())).ToString("N0", nfi);
                    xuat += double.Parse(Grid["GiaXuat", i].Value.ToString()) * double.Parse(Grid["SLXuat", i].Value.ToString());
                    //  rang.Value2 = "=h" + rowIndex.ToString() + "*i" + rowIndex.ToString();

                    rowIndex += 1;
                }
                rang = _xlWorkSheet.get_Range("c7", "l" + (Grid.Rows.Count + 9));
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;
                //Đóng khung bảng thực phẩm
                rang = _xlWorkSheet.get_Range("a" + (startIndex - 2), "l" + (rowIndex - 1));
                rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlContinuous;


                //Dòng tổng kết của bảng thực phẩm
                rowIndex += 1;




                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Select();
                rang.Value2 = "Tổng";
                rang.RowHeight = 17.25;
                rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "'" + truoc.ToString("N0", nfi);
                //    rang.Value2 = "=sum(d" + startIndex.ToString() + ":d" + (rowIndex - 2).ToString() + ")";

                rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "'" + nhap.ToString("N0", nfi);
                //    rang.Value2 = "=sum(g" + startIndex.ToString() + ":g" + (rowIndex - 2).ToString() + ")";

                rang = _xlWorkSheet.get_Range("j" + rowIndex, System.Type.Missing);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "'" + xuat.ToString("N0", nfi);
                // rang.Value2 = "=sum(j" + startIndex.ToString() + ":j" + (rowIndex - 2).ToString() + ")";

                rang = _xlWorkSheet.get_Range("l" + rowIndex, System.Type.Missing);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "'" + cuoingay.ToString("N0", nfi);
                // rang.Value2 = "=sum(l" + startIndex.ToString() + ":l" + (rowIndex - 2).ToString() + ")";
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.Turquoise);
                rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.PaleGoldenrod);

                rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Font.Bold = true;
                rowIndex += 1;
                #endregion


                _xlWorkSheet.get_Range("H4", "H4").Value2 = "Nhà trẻ: " + iTongsotre_nt.ToString();
                _xlWorkSheet.get_Range("J4", "J4").Value2 = "Mẫu giáo: " + iTongsotre_mg.ToString();
                _xlWorkSheet.get_Range("E4", "E4").Value2 = "Tổng số trẻ: " + (iTongsotre_nt + iTongsotre_mg).ToString();

                rang = _xlWorkSheet.get_Range("a4", "l4");

                //     rang.BorderAround(exel.XlLineStyle.xlContinuous, exel.XlBorderWeight.xlThin, exel.XlColorIndex.xlColorIndexAutomatic, exel.XlColorIndex.xlColorIndexAutomatic);
                rang.RowHeight = 20;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                rang = _xlWorkSheet.get_Range("d3", "d3");
                rang.ColumnWidth = 13;
                //Xuất thực đơn
                ClsTblThamsodauvaoBLL ClsTblThamsodauvaobll = new ClsTblThamsodauvaoBLL();
                if (ClsTblThamsodauvaobll.SelectAll().Rows.Count > 0 && ClsTblThamsodauvaobll.SelectAll().Rows[0]["Baoanchitiet"].ToString() == "True")
                {
                    #region "Xuat thuc don"
                    System.Data.DataTable dtThucdon = ClsThucdon_monanBLL.GetByDate4Report(dateReport);
                    System.Data.DataTable dtBuatrongngay = ClsThucdon_monanBLL.GetByDate4Report(dateReport);

                    rowIndex += 1;
                    startIndex = rowIndex;
                    rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                    rang.Value2 = "Thực đơn";

                    rang = _xlWorkSheet.get_Range("b" + rowIndex, "c" + rowIndex);
                    rang.Select();
                    rang.Merge(false);
                    rang.Value2 = "Nhà trẻ";

                    rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                    rang.Value2 = "Được chi";

                    rang = _xlWorkSheet.get_Range("e" + rowIndex, "g" + rowIndex);
                    rang.Select();
                    rang.Merge(false);
                    rang.Value2 = "Mẫu giáo";

                    rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                    rang.Value2 = "Được chi";

                    rang = _xlWorkSheet.get_Range("i" + rowIndex, "l" + rowIndex);
                    rang.Select();
                    rang.Merge(false);
                    rang.Value2 = "Quyết toán trong ngày";

                    rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                    rang.Font.Bold = true;
                    rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                    rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                    rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.Turquoise);


                    System.Data.DataView dv = dtThucdon.DefaultView;
                    System.Data.DataTable dtBuaan = ClsTblDm_BuaantrongngayBLL.GetAll();
                    int sotien_nt = 0;
                    int sotien_mg = 0;
                    for (int i = 0; i < dtBuaan.Rows.Count; i++)
                    {
                        rowIndex += 1;

                        rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                        rang.Value2 = dtBuaan.Rows[i]["Tenbuaan"].ToString();
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        dv.RowFilter = " KhoilopID = 1 and buaantrongngayid = " + dtBuaan.Rows[i]["id"].ToString();
                        rang = _xlWorkSheet.get_Range("b" + rowIndex, "c" + rowIndex);
                        rang.Select();
                        rang.Merge(false);
                        rang.WrapText = true;
                        string tenmon = "";
                        for (int j = 0; j < dv.Count; j++)
                        {
                            if (dv[j]["Tenmonan"] != null)
                                tenmon += dv[j]["Tenmonan"].ToString() + ", ";
                        }
                        rang.Value2 = tenmon;
                        rang.Font.Size = 8;
                        rang.RowHeight = (12.75 * (dv.Count + 1));
                        _xlWorkSheet.get_Range("d" + rowIndex, "d" + rowIndex).Value2 = "'" + ((int.Parse(chi_nt) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100).ToString("N0", nfi);
                        sotien_nt += ((int.Parse(chi_nt) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100);
                        rang = _xlWorkSheet.get_Range("d" + rowIndex, "d" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                        dv.RowFilter = " KhoilopID = 2 and buaantrongngayid = " + dtBuaan.Rows[i]["id"].ToString();
                        rang = _xlWorkSheet.get_Range("e" + rowIndex, "g" + rowIndex);
                        rang.Select();
                        rang.Merge(false);
                        rang.WrapText = true;
                        tenmon = "";
                        for (int j = 0; j < dv.Count; j++)
                        {
                            if (dv[j]["Tenmonan"] != null)
                                tenmon += dv[j]["Tenmonan"].ToString() + ", ";
                        }
                        rang.Value2 = tenmon;
                        rang.Font.Size = 8;
                        _xlWorkSheet.get_Range("h" + rowIndex, "h" + rowIndex).Value2 = "'" + ((int.Parse(chi_mg) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100).ToString("N0", nfi);
                        sotien_mg += ((int.Parse(chi_mg) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100);
                        rang = _xlWorkSheet.get_Range("h" + rowIndex, "h" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;



                        rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                        rang.RowHeight = 48;
                        rang = _xlWorkSheet.get_Range("b" + rowIndex, "b" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
                        rang = _xlWorkSheet.get_Range("e" + rowIndex, "e" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
                        //      rang.RowHeight = (12.75 * (dv.Count + 1));
                    }

                    if (rowIndex - startIndex >= 4)
                    {
                        rang = _xlWorkSheet.get_Range("i" + (rowIndex - 3), "k" + (rowIndex - 3));
                        rang.Merge(false);
                        rang.Value2 = "Tồn hôm trước mang sang:";

                        rang = _xlWorkSheet.get_Range("i" + (rowIndex - 2), "k" + (rowIndex - 2));
                        rang.Merge(false);
                        rang.Value2 = "Tiêu chuẩn được chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        rang = _xlWorkSheet.get_Range("l" + (rowIndex - 2), System.Type.Missing);
                        rang.Merge(false);
                        // rang.Value2 = "=sum(d" + startIndex.ToString() + ":d" + rowIndex.ToString() + ") + sum(h" + startIndex + ":h" + rowIndex.ToString() + ")";
                        rang.Value2 = "'" + (double.Parse(chi_nt) + double.Parse(chi_mg)).ToString("N0", nfi);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                        rang = _xlWorkSheet.get_Range("i" + (rowIndex - 1), "k" + (rowIndex - 1));
                        rang.Merge(false);
                        rang.Value2 = "Đã chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                        rang = _xlWorkSheet.get_Range("i" + rowIndex, "k" + rowIndex);
                        rang.Merge(false);
                        rang.Value2 = "Thừa thiếu:";



                    }
                    else
                    {
                        rang = _xlWorkSheet.get_Range("i" + startIndex, "k" + startIndex);
                        rang.Merge(false);
                        rang.Value2 = "Tồn hôm trước mang sang:";

                        rang = _xlWorkSheet.get_Range("i" + (startIndex + 1), "k" + (startIndex + 1));
                        rang.Merge(false);
                        rang.Value2 = "Tiêu chuẩn được chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        rang = _xlWorkSheet.get_Range("l" + (startIndex + 1), System.Type.Missing);
                        rang.Merge(false);
                        // rang.Value2 = "=sum(d" + startIndex.ToString() + ":d" + rowIndex.ToString() + ") + sum(h" + startIndex + ":h" + rowIndex.ToString() + ")";
                        rang.Value2 = "'" + (double.Parse(chi_nt) + double.Parse(chi_mg)).ToString("N0", nfi);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                        rang = _xlWorkSheet.get_Range("i" + (startIndex + 2), "k" + (startIndex + 2));
                        rang.Merge(false);
                        rang.Value2 = "Đã chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                        rang = _xlWorkSheet.get_Range("i" + (startIndex + 3), "k" + (startIndex + 3));
                        rang.Merge(false);
                        rang.Value2 = "Thừa thiếu:";
                    }


                    rang = _xlWorkSheet.get_Range("a" + startIndex, "l" + rowIndex);
                    rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                    rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                    rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlContinuous;



                    #endregion

                    rang = _xlWorkSheet.get_Range("A" + (10 + Grid.RowCount), "L" + (10 + Grid.RowCount));
                    rang.RowHeight = 24;
                    rang.WrapText = true;

                    _xlWorkSheet.get_Range("D" + (10 + Grid.RowCount), "D" + (10 + Grid.RowCount)).Value2 = "Được chi " + (double.Parse(chi_nt)).ToString("N0", nfi);
                    _xlWorkSheet.get_Range("H" + (10 + Grid.RowCount), "H" + (10 + Grid.RowCount)).Value2 = "Được chi " + (double.Parse(chi_mg)).ToString("N0", nfi);

                }
                else
                {
                    #region "Xuat thuc don"
                    System.Data.DataTable dtThucdon = ClsThucdon_monanBLL.GetByDate4Report(dateReport);
                    System.Data.DataTable dtBuatrongngay = ClsThucdon_monanBLL.GetByDate4Report(dateReport);

                    rowIndex += 1;
                    startIndex = rowIndex;
                    rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                    rang.Value2 = "Thực đơn";

                    //rang = _xlWorkSheet.get_Range("b" + rowIndex, "c" + rowIndex);
                    //rang.Select();
                    //rang.Merge(false);
                    //rang.Value2 = "Nhà trẻ";

                    //rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                    //rang.Value2 = "Được chi";

                    //rang = _xlWorkSheet.get_Range("e" + rowIndex, "g" + rowIndex);
                    //rang.Select();
                    //rang.Merge(false);
                    //rang.Value2 = "Mẫu giáo";
                    rang = _xlWorkSheet.get_Range("b" + rowIndex, "g" + rowIndex);
                    rang.Select();
                    rang.Merge(false);
                    rang.Value2 = "Toàn trường";

                    rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                    rang.Value2 = "Được chi";

                    rang = _xlWorkSheet.get_Range("i" + rowIndex, "l" + rowIndex);
                    rang.Select();
                    rang.Merge(false);
                    rang.Value2 = "Quyết toán trong ngày";

                    rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                    rang.Font.Bold = true;
                    rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                    rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                    rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.Turquoise);


                    System.Data.DataView dv = dtThucdon.DefaultView;
                    System.Data.DataTable dtBuaan = ClsTblDm_BuaantrongngayBLL.GetAll();
                    int sotien_nt = 0;
                    int sotien_mg = 0;
                    ClsTblGiaBLL ClsTblGiabll = new ClsTblGiaBLL();
                    int giatien = 0; int tongsotre = 0;
                    if (ClsTblGiabll.ReturnTblSelectAll().Rows.Count > 0)
                    {
                        giatien = int.Parse(ClsTblGiabll.ReturnTblSelectAll().Rows[0]["maugiao"].ToString());
                    }
                    tongsotre = iTongsotre_mg + iTongsotre_nt;

                    for (int i = 0; i < dtBuaan.Rows.Count; i++)
                    {
                        rowIndex += 1;

                        rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                        rang.Value2 = dtBuaan.Rows[i]["Tenbuaan"].ToString();
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        dv.RowFilter = " KhoilopID = 2 and buaantrongngayid = " + dtBuaan.Rows[i]["id"].ToString();
                        rang = _xlWorkSheet.get_Range("b" + rowIndex, "g" + rowIndex);
                        rang.Select();
                        rang.Merge(false);
                        rang.WrapText = true;
                        string tenmon = "";
                        for (int j = 0; j < dv.Count; j++)
                        {
                            if (dv[j]["Tenmonan"] != null)
                                tenmon += dv[j]["Tenmonan"].ToString() + ", ";
                        }
                        rang.Value2 = tenmon;
                        rang.Font.Size = 8;
                        rang.RowHeight = (12.75 * (dv.Count + 1));
                        _xlWorkSheet.get_Range("d" + rowIndex, "d" + rowIndex).Value2 = "'" + ((int.Parse(chi_nt) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100).ToString("N0", nfi);
                        sotien_nt += ((int.Parse(chi_nt) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100);
                        rang = _xlWorkSheet.get_Range("d" + rowIndex, "d" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                        //dv.RowFilter = " KhoilopID = 2 and buaantrongngayid = " + dtBuaan.Rows[i]["id"].ToString();
                        //rang = _xlWorkSheet.get_Range("e" + rowIndex, "g" + rowIndex);
                        //rang.Select();
                        //rang.Merge(false);
                        //rang.WrapText = true;
                        //tenmon = "";
                        //for (int j = 0; j < dv.Count; j++)
                        //{
                        //    if (dv[j]["Tenmonan"] != null)
                        //        tenmon += dv[j]["Tenmonan"].ToString() + ", ";
                        //}
                        //rang.Value2 = tenmon;
                        //rang.Font.Size = 8;



                        _xlWorkSheet.get_Range("h" + rowIndex, "h" + rowIndex).Value2 = "'" + (((double.Parse(chi_nt) + double.Parse(chi_mg)) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100).ToString("N0", nfi);
                        sotien_mg += ((int.Parse(chi_mg) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100);
                        //         sotien_mg += (((tongsotre*giatien) * int.Parse(dtBuaan.Rows[i]["Phanbotienan"].ToString())) / 100);

                        rang = _xlWorkSheet.get_Range("h" + rowIndex, "h" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;



                        rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                        rang.RowHeight = 48;
                        rang = _xlWorkSheet.get_Range("b" + rowIndex, "b" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
                        rang = _xlWorkSheet.get_Range("e" + rowIndex, "e" + rowIndex);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
                        //      rang.RowHeight = (12.75 * (dv.Count + 1));
                    }

                    if (rowIndex - startIndex >= 4)
                    {
                        rang = _xlWorkSheet.get_Range("i" + (rowIndex - 3), "k" + (rowIndex - 3));
                        rang.Merge(false);
                        rang.Value2 = "Tồn hôm trước mang sang:";

                        rang = _xlWorkSheet.get_Range("i" + (rowIndex - 2), "k" + (rowIndex - 2));
                        rang.Merge(false);
                        rang.Value2 = "Tiêu chuẩn được chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        rang = _xlWorkSheet.get_Range("l" + (rowIndex - 2), System.Type.Missing);
                        rang.Merge(false);
                        // rang.Value2 = "=sum(d" + startIndex.ToString() + ":d" + rowIndex.ToString() + ") + sum(h" + startIndex + ":h" + rowIndex.ToString() + ")";
                        rang.Value2 = "'" + (double.Parse(chi_mg) + double.Parse(chi_nt)).ToString("N0", nfi);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                        rang = _xlWorkSheet.get_Range("i" + (rowIndex - 1), "k" + (rowIndex - 1));
                        rang.Merge(false);
                        rang.Value2 = "Đã chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                        rang = _xlWorkSheet.get_Range("i" + rowIndex, "k" + rowIndex);
                        rang.Merge(false);
                        rang.Value2 = "Thừa thiếu:";



                    }
                    else
                    {
                        rang = _xlWorkSheet.get_Range("i" + startIndex, "k" + startIndex);
                        rang.Merge(false);
                        rang.Value2 = "Tồn hôm trước mang sang:";

                        rang = _xlWorkSheet.get_Range("i" + (startIndex + 1), "k" + (startIndex + 1));
                        rang.Merge(false);
                        rang.Value2 = "Tiêu chuẩn được chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        rang = _xlWorkSheet.get_Range("l" + (startIndex + 1), System.Type.Missing);
                        rang.Merge(false);
                        // rang.Value2 = "=sum(d" + startIndex.ToString() + ":d" + rowIndex.ToString() + ") + sum(h" + startIndex + ":h" + rowIndex.ToString() + ")";
                        rang.Value2 = "'" + (double.Parse(chi_mg) + double.Parse(chi_nt)).ToString("N0", nfi);
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                        rang = _xlWorkSheet.get_Range("i" + (startIndex + 2), "k" + (startIndex + 2));
                        rang.Merge(false);
                        rang.Value2 = "Đã chi:";
                        rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                        rang = _xlWorkSheet.get_Range("i" + (startIndex + 3), "k" + (startIndex + 3));
                        rang.Merge(false);
                        rang.Value2 = "Thừa thiếu:";
                    }


                    rang = _xlWorkSheet.get_Range("a" + startIndex, "l" + rowIndex);
                    rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                    rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                    rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlContinuous;



                    #endregion

                    rang = _xlWorkSheet.get_Range("A" + (10 + Grid.RowCount), "L" + (10 + Grid.RowCount));
                    rang.RowHeight = 24;
                    rang.WrapText = true;

                    //    _xlWorkSheet.get_Range("D" + (10 + Grid.RowCount), "D" + (10 + Grid.RowCount)).Value2 = "Được chi " + (double.Parse(chi_nt)).ToString("N0", nfi);
                    _xlWorkSheet.get_Range("H" + (10 + Grid.RowCount), "H" + (10 + Grid.RowCount)).Value2 = "Được chi " + (double.Parse(chi_nt) + double.Parse(chi_mg)).ToString("N0", nfi);

                }



                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "b" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Người lập";
                rang.RowHeight = 22.5;

                rang = _xlWorkSheet.get_Range("c" + rowIndex, "g" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Người nấu bếp";

                rang = _xlWorkSheet.get_Range("h" + rowIndex, "l" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Đại diện BGH";

                rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                rang.Font.Bold = true;
                rang.Font.Italic = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;


                rowIndex += 4;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "e" + rowIndex);
                rang.Font.Bold = true;
                rang.Font.Italic = true;
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Chi phí phát sinh : " + (double.Parse(sChiphiphatsinh)).ToString("N0", nfi) + " (VNĐ)";

                _xlWorkSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                _xlWorkSheet.PageSetup.CenterHorizontally = true;
                _xlWorkSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;

                rang = _xlWorkSheet.get_Range("E4", "E4");
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang = _xlWorkSheet.get_Range("A5", "A5");
                rang.VerticalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.RowHeight = 21;
                rang = _xlWorkSheet.get_Range("B5", "B5");
                rang.VerticalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang = _xlWorkSheet.get_Range("C5", "K5");
                rang.VerticalAlignment = exel.XlHAlign.xlHAlignCenter;

                for (int a = 1; a <= 11; a++)
                {
                    if (a == 1 || a == 3 || a == 8 || a == 11)
                        AutoFitColumn(a, _xlWorkSheet);
                }
            }
            catch (System.Exception ex)
            { MessageBox.Show("loi export file exle " + ex.Message); }
        }
        private static void AutoFitColumn(int column, Microsoft.Office.Interop.Excel._Worksheet sheet)
        {
            ((exel.Range)sheet.Cells[1, column]).EntireColumn.AutoFit();
        }
        public static void ExportThucdontrongtuan(DateTime dateReport)
        {
            int rowIndex = 1;

            _exlApplication = new exel.ApplicationClass();
            _exlApplication.Visible = true;
            _xlWorkBook = _exlApplication.Workbooks.Add(Value);
            _xlWorkSheet = (exel.Worksheet)_xlWorkBook.Worksheets[1];
            //_xlWorkSheet.Cells.Font.Name = "Arial";
            _xlWorkSheet.Cells.Font.Name = "Times new roman";
            _xlWorkSheet.Cells.Font.Size = 12;
            _xlWorkSheet.Activate();

            try
            {
                // Căn chỉnh kiểu hiển thị và độ rộng cột
                #region "Can chinh kieu hien thi va do rong cot"
                rowIndex += 1;
                rang = (exel.Range)_xlWorkSheet.Columns["a", System.Type.Missing];
                rang.ColumnWidth = 10;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["b", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["c", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["d", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["e", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["f", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["g", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["h", System.Type.Missing];
                rang.ColumnWidth = 17;
                #endregion


                //export to rowIndex = 1 => xuất phát từ dòng đầu tiên ==Tên phòng GD & tên trường

                System.Data.DataTable dtTruong = new ClsTblTruonghocBLL().ReturnTblTRuong();
                rowIndex = 1;
                #region "Xuat dong Tên trường & Tên báo cáo"
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "d" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = dtTruong.Rows[0]["TenphongGD"].ToString().ToUpper() + "\n" + dtTruong.Rows[0]["Tentruong"].ToString().ToUpper();
                rang.Font.Bold = true;
                rang.Font.Size = 10;
                rang.RowHeight = 30;
                rowIndex += 1;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, "f" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "THỰC ĐƠN NHÀ TRẺ & MẪU GIÁO";
                rang.Font.Bold = true;
                rang.Font.Size = 16;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rowIndex += 1;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, "f" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tháng " + dateReport.Month + " năm " + dateReport.Year;
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rowIndex += 1;
                #endregion

                #region "Xuat tieu de"
                int startIndex = rowIndex;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Value2 = "Khối lớp";
                rang.Font.Bold = true;
                rang.RowHeight = 30;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thời gian";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("c" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 2";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 3";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("e" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 4";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("f" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 5";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 6";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 7";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                #endregion

                //Xuất thực đơn
                #region "Xuat thuc don"
                System.Data.DataTable dtThucdon = ClsThucdon_monanBLL.GetByWeek4Report(dateReport);
                System.Data.DataTable dtBuatrongngay = ClsThucdon_monanBLL.GetByDate4Report(dateReport);


                rang = _xlWorkSheet.get_Range("a" + rowIndex, "h" + rowIndex);
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;


                System.Data.DataView dv = dtThucdon.DefaultView;
                System.Data.DataTable dtBuaan = ClsTblDm_BuaantrongngayBLL.GetAll();
                System.Data.DataTable dtKhoilop = ClsThucdon_monanBLL.GetKhoilop();
                for (int countKhoilop = 0; countKhoilop < dtKhoilop.Rows.Count; countKhoilop++)
                {
                    rowIndex += 1;
                    string conditionKhoilop = " KhoilopID = " + dtKhoilop.Rows[countKhoilop]["id"].ToString();

                    rang = _xlWorkSheet.get_Range("a" + rowIndex, "a" + (rowIndex + dtBuaan.Rows.Count - 1));
                    rang.Merge(false);
                    rang.Value2 = dtKhoilop.Rows[countKhoilop]["tenkhoilop"].ToString();
                    rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                    rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                    rang.Font.Bold = true;

                    object[] colName = { 'c', 'd', 'e', 'f', 'g', 'h' };
                    for (int i = 0; i < dtBuaan.Rows.Count; i++)
                    {
                        rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                        rang.Value2 = dtBuaan.Rows[i]["Tenbuaan"].ToString();
                        rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                        rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                        rang.RowHeight = 70;

                        DateTime binDate = dateReport.AddDays(-(double)dateReport.DayOfWeek + 1);

                        for (int dayofweek = 0; dayofweek <= 5; dayofweek++)
                        {
                            dv = dtThucdon.DefaultView;
                            dv.RowFilter = conditionKhoilop + " and buaantrongngayid = " + dtBuaan.Rows[i]["id"].ToString() +
                                            " and Ngay >= '" + binDate + "' " + " and Ngay < '" + binDate.AddDays(1) + "' ";
                            rang = _xlWorkSheet.get_Range(colName[dayofweek].ToString() + rowIndex.ToString(), System.Type.Missing);
                            rang.Select();
                            rang.Merge(false);
                            rang.WrapText = true;
                            string tenmon = "";
                            for (int j = 0; j < dv.Count; j++)
                            {
                                if (dv[j]["Tenmonan"] != null)
                                    tenmon += dv[j]["Tenmonan"].ToString() + ", ";
                            }

                            rang.Value2 = tenmon == "" ? "" : tenmon.Substring(0, tenmon.Length - 2);
                            rang.Font.Size = 12;
                            rang.VerticalAlignment = exel.XlVAlign.xlVAlignTop;
                            binDate = binDate.AddDays(1);
                        }

                        rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                        rang.Rows.AutoFit();
                        rowIndex += 1;
                    }
                    rowIndex -= 1;
                }
                rang = _xlWorkSheet.get_Range("a" + startIndex, "h" + rowIndex);
                rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlContinuous;
                #endregion

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("g" + rowIndex, "h" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Ngày " + dateReport.Day + " tháng " + dateReport.Month + " năm " + dateReport.Year;
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("g" + rowIndex, "h" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tổ nuôi dưỡng";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                _xlWorkSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                _xlWorkSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                _xlWorkSheet.PageSetup.CenterHorizontally = true;
            }
            catch (System.Exception ex)
            { MessageBox.Show("loi export file exle " + ex.Message); }
        }
        public static void ExportThucdontrongtuan_truong(DateTime dateReport, int KhoilopId, string tenkhoi)
        {
            int rowIndex = 1;

            _exlApplication = new exel.ApplicationClass();
            _exlApplication.Visible = true;
            _xlWorkBook = _exlApplication.Workbooks.Add(Value);
            _xlWorkSheet = (exel.Worksheet)_xlWorkBook.Worksheets[1];
            //_xlWorkSheet.Cells.Font.Name = "Arial";
            _xlWorkSheet.Cells.Font.Name = "Times new roman";
            _xlWorkSheet.Cells.Font.Size = 12;
            _xlWorkSheet.Activate();

            try
            {
                // Căn chỉnh kiểu hiển thị và độ rộng cột
                #region "Can chinh kieu hien thi va do rong cot"
                rowIndex += 1;
                rang = (exel.Range)_xlWorkSheet.Columns["a", System.Type.Missing];
                rang.ColumnWidth = 10;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["b", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["c", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["d", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["e", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["f", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["g", System.Type.Missing];
                rang.ColumnWidth = 17;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["h", System.Type.Missing];
                rang.ColumnWidth = 17;
                #endregion


                //export to rowIndex = 1 => xuất phát từ dòng đầu tiên ==Tên phòng GD & tên trường

                System.Data.DataTable dtTruong = new ClsTblTruonghocBLL().ReturnTblTRuong();
                rowIndex = 1;
                #region "Xuat dong Tên trường & Tên báo cáo"
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "d" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = dtTruong.Rows[0]["TenphongGD"].ToString().ToUpper() + "\n" + dtTruong.Rows[0]["Tentruong"].ToString().ToUpper();
                rang.Font.Bold = true;
                rang.Font.Size = 10;
                rang.RowHeight = 30;
                rowIndex += 1;

                rang = _xlWorkSheet.get_Range("c" + rowIndex, "g" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "THỰC ĐƠN TUẦN CỦA " + tenkhoi.ToUpper();
                rang.Font.Bold = true;
                rang.Font.Size = 16;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rowIndex += 1;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, "f" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tháng " + dateReport.Month + " năm " + dateReport.Year;
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rowIndex += 1;
                #endregion

                #region "Xuat tieu de"
                int startIndex = rowIndex;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Value2 = "Khối lớp";
                rang.Font.Bold = true;
                rang.RowHeight = 30;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thời gian";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("c" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 2";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 3";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("e" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 4";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("f" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 5";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("g" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 6";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                rang = _xlWorkSheet.get_Range("h" + rowIndex, System.Type.Missing);
                rang.Value2 = "Thứ 7";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;

                #endregion

                //Xuất thực đơn
                #region "Xuat thuc don"
                System.Data.DataTable dtThucdon = ClsThucdon_monanBLL.GetByWeek4Report(dateReport);
                System.Data.DataTable dtBuatrongngay = ClsThucdon_monanBLL.GetByDate4Report(dateReport);


                rang = _xlWorkSheet.get_Range("a" + rowIndex, "h" + rowIndex);
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;


                System.Data.DataView dv = dtThucdon.DefaultView;
                System.Data.DataTable dtBuaan = ClsTblDm_BuaantrongngayBLL.GetAll();
                System.Data.DataTable dtKhoilop = ClsThucdon_monanBLL.GetKhoilop(KhoilopId);
                for (int countKhoilop = 0; countKhoilop < dtKhoilop.Rows.Count; countKhoilop++)
                {
                    rowIndex += 1;
                    string conditionKhoilop = " KhoilopID = " + dtKhoilop.Rows[countKhoilop]["id"].ToString();

                    rang = _xlWorkSheet.get_Range("a" + rowIndex, "a" + (rowIndex + dtBuaan.Rows.Count - 1));
                    rang.Merge(false);
                    //      rang.Value2 = dtKhoilop.Rows[countKhoilop]["tenkhoilop"].ToString();
                    rang.Value2 = tenkhoi;
                    rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                    rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                    rang.Font.Bold = true;

                    object[] colName = { 'c', 'd', 'e', 'f', 'g', 'h' };
                    for (int i = 0; i < dtBuaan.Rows.Count; i++)
                    {
                        rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                        rang.Value2 = dtBuaan.Rows[i]["Tenbuaan"].ToString();
                        rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                        rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;
                        rang.RowHeight = 70;

                        DateTime binDate = dateReport.AddDays(-(double)dateReport.DayOfWeek + 1);

                        for (int dayofweek = 0; dayofweek <= 5; dayofweek++)
                        {
                            dv = dtThucdon.DefaultView;
                            dv.RowFilter = conditionKhoilop + " and buaantrongngayid = " + dtBuaan.Rows[i]["id"].ToString() +
                                            " and Ngay >= '" + binDate.ToString("MM/d/yyyy") + "' " + " and Ngay < '" + binDate.AddDays(1).ToString("MM/d/yyyy") + "' ";
                            rang = _xlWorkSheet.get_Range(colName[dayofweek].ToString() + rowIndex.ToString(), System.Type.Missing);
                            rang.Select();
                            rang.Merge(false);
                            rang.WrapText = true;
                            string tenmon = "";
                            for (int j = 0; j < dv.Count; j++)
                            {
                                if (dv[j]["Tenmonan"] != null)
                                    tenmon += dv[j]["Tenmonan"].ToString() + ", ";
                            }

                            rang.Value2 = tenmon == "" ? "" : tenmon.Substring(0, tenmon.Length - 2);
                            rang.Font.Size = 12;
                            rang.VerticalAlignment = exel.XlVAlign.xlVAlignTop;
                            binDate = binDate.AddDays(1);
                        }

                        rang = _xlWorkSheet.get_Range("a" + rowIndex, "l" + rowIndex);
                        rang.Rows.AutoFit();
                        rowIndex += 1;
                    }
                    rowIndex -= 1;
                }
                rang = _xlWorkSheet.get_Range("a" + startIndex, "h" + rowIndex);
                rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlContinuous;
                #endregion

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("g" + rowIndex, "h" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Ngày " + dateReport.Day + " tháng " + dateReport.Month + " năm " + dateReport.Year;
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("g" + rowIndex, "h" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Tổ nuôi dưỡng";
                rang.Font.Bold = true;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                _xlWorkSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                _xlWorkSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                _xlWorkSheet.PageSetup.CenterHorizontally = true;
                AutoFitColumn(1, _xlWorkSheet);
            }
            catch (System.Exception ex)
            { MessageBox.Show("loi export file exle " + ex.Message); }
        }

        public static void ExportBienlai(DateTime dateReport, int ThangthuID, int NamtrehocID, string tenTre, string soBL, string tenLop, int soPhieudoi, int soPhieumua, double giaPhieu)
        {
            int rowIndex = 1;

            _exlApplication = new exel.ApplicationClass();
            _exlApplication.Visible = true;
            _xlWorkBook = _exlApplication.Workbooks.Add(Value);
            _xlWorkSheet = (exel.Worksheet)_xlWorkBook.Worksheets[1];
            _xlWorkSheet.Cells.Font.Name = "Arial";
            NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
            nfi.NumberGroupSeparator = ".";
            nfi.NumberDecimalSeparator = ",";

            _xlWorkSheet.Activate();

            try
            {
                // Căn chỉnh kiểu hiển thị và độ rộng cột
                #region "Can chinh kieu hien thi va do rong cot"
                rowIndex += 1;
                rang = (exel.Range)_xlWorkSheet.Columns["a", System.Type.Missing];
                rang.ColumnWidth = 15;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["b", System.Type.Missing];
                rang.ColumnWidth = 11; rang.NumberFormat = "#,##0";
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["c", System.Type.Missing];
                rang.ColumnWidth = 11;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["d", System.Type.Missing];
                rang.ColumnWidth = 13;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["e", System.Type.Missing];
                rang.ColumnWidth = 13;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = (exel.Range)_xlWorkSheet.Columns["f", System.Type.Missing];
                rang.ColumnWidth = 12; rang.NumberFormat = "#,##0";
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;
                rang = (exel.Range)_xlWorkSheet.Columns["g", System.Type.Missing];
                rang.ColumnWidth = 8;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                #endregion

                //export to rowIndex = 1 => xuất phát từ dòng đầu tiên ==Tên phòng GD & tên trường

                System.Data.DataTable dtTruong = new ClsTblTruonghocBLL().ReturnTblTRuong();
                rowIndex = 1;
                #region "Xuat dong Tên trường & Tên báo cáo"
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "c" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = dtTruong.Rows[0]["TenphongGD"].ToString().ToUpper() + "\n" + dtTruong.Rows[0]["Tentruong"].ToString().ToUpper();
                rang.Font.Size = 10;
                rang.RowHeight = 30;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, "g" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM\nĐộc lập - Tự do - Hạnh phúc";
                rang.Font.Bold = true;
                rang.Font.Size = 10;
                rang.RowHeight = 30;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rowIndex += 1;
                rowIndex += 1;




                rang = _xlWorkSheet.get_Range("c" + rowIndex, "e" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "BIÊN LAI";
                rang.Font.Bold = true;
                rang.Font.Size = 16;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;
                rowIndex += 1;

                rang = _xlWorkSheet.get_Range("a" + rowIndex, "e" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Họ tên học sinh: " + tenTre;
                rang.Font.Bold = true;
                rang.Font.Size = 12;
                rang.RowHeight = 30;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rang = _xlWorkSheet.get_Range("f" + (rowIndex - 1), "g" + rowIndex);
                rang.Select();
                rang.Merge(false);
                rang.Value2 = "Số: " + soBL + "\n" + "Lớp: " + tenLop;
                rang.Font.Italic = true; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignTop;
                #endregion

                #region "Phiếu ăn"
                rowIndex += 1;
                int startIndex = rowIndex;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Value2 = "Số phiếu đổi: "; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                rang.Value2 = soPhieudoi;
                rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Value2 = "Số phiếu mua: "; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                rang.Value2 = soPhieumua;
                rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Value2 = "Đơn giá: "; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                rang.Value2 = "'" + giaPhieu.ToString("N0", nfi);
                rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, System.Type.Missing);
                rang.Value2 = "Cộng:";
                rang.Font.Italic = true; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                rang = _xlWorkSheet.get_Range("b" + rowIndex, System.Type.Missing);
                rang.Value2 = "'" + ((soPhieumua - soPhieudoi) * giaPhieu).ToString("N0", nfi);
                rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = _xlWorkSheet.get_Range("a" + (startIndex), "b" + rowIndex);
                rang.Borders.LineStyle = exel.XlLineStyle.xlContinuous;
                rang.Borders[exel.XlBordersIndex.xlInsideHorizontal].LineStyle = exel.XlLineStyle.xlLineStyleNone;
                rang.Borders[exel.XlBordersIndex.xlInsideVertical].LineStyle = exel.XlLineStyle.xlLineStyleNone;

                #endregion

                #region "Tiền khác"
                rowIndex = startIndex;
                rang = _xlWorkSheet.get_Range("d" + rowIndex, System.Type.Missing);
                rang.Merge(false);
                rang.Value2 = "'- Tiền ăn: "; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                rang = _xlWorkSheet.get_Range("f" + rowIndex, "f" + rowIndex);
                rang.Merge(false); rang.Font.Size = 12;
                rang.Value2 = "=b" + (rowIndex + 3);
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;
                double tongtienkhac = 0;
                System.Data.DataTable dtThuthang = new System.Data.DataTable();// = ClsTblTrephaithuthangBLL.GetListByNamtrehocId(ThangthuID, NamtrehocID);
                rowIndex += 1;
                for (int countThuthang = 0; countThuthang < dtThuthang.Rows.Count; countThuthang++)
                {
                    rang = _xlWorkSheet.get_Range("d" + rowIndex, "e" + rowIndex);
                    rang.Merge(false); rang.Font.Size = 12;
                    rang.Value2 = "'- " + dtThuthang.Rows[countThuthang]["Tenmucthu"] + ": ";
                    rang.HorizontalAlignment = exel.XlHAlign.xlHAlignLeft;

                    rang = _xlWorkSheet.get_Range("f" + rowIndex, System.Type.Missing);
                    rang.Merge(false); rang.Font.Size = 12;
                    rang.Value2 = "'" + ((double)dtThuthang.Rows[countThuthang]["Danop"]).ToString("N0", nfi);
                    tongtienkhac += (double)dtThuthang.Rows[countThuthang]["Danop"];
                    rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;
                    rowIndex += 1;
                }
                #endregion

                if (rowIndex < startIndex + 4)
                    rowIndex = startIndex + 4;

                rang = _xlWorkSheet.get_Range("d" + rowIndex, "e" + rowIndex);
                rang.Merge(false);
                rang.Value2 = " Cộng:  ";
                rang.Font.Italic = true; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                rang = _xlWorkSheet.get_Range("f" + rowIndex, System.Type.Missing);
                rang.Merge(false); rang.Font.Size = 12;
                //      rang.Value2 = "=sum(f" + startIndex + ":f" + (rowIndex - 1) + ")";
                rang.Value2 = "'" + (tongtienkhac + (soPhieumua - soPhieudoi) * giaPhieu).ToString("N0", nfi);
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;

                //        double tongso = (double)rang.Value2;
                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "g" + rowIndex);
                rang.Merge(false);
                rang.WrapText = true;
                rang.Value2 = "(Bằng chữ: " + Util.docso((tongtienkhac + (soPhieumua - soPhieudoi) * giaPhieu).ToString()) + ")";
                rang.Font.Italic = true; rang.Font.Size = 12;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "g" + rowIndex);
                rang.Rows.AutoFit();
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignTop;

                rowIndex += 1;
                rang = _xlWorkSheet.get_Range("a" + rowIndex, "g" + rowIndex);
                rang.Merge(false);
                rang.Value2 = "Ngày " + dateReport.Day + " tháng " + dateReport.Month + " năm " + dateReport.Year;
                rang.Font.Italic = true; rang.Font.Size = 12;
                rang.RowHeight = 30;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignRight;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rowIndex += 1;

                rang = _xlWorkSheet.get_Range("a" + rowIndex, "b" + rowIndex);
                rang.Merge(false);
                rang.Value2 = "Người nộp";
                rang.Font.Bold = true; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rang = _xlWorkSheet.get_Range("c" + rowIndex, "e" + rowIndex);
                rang.Merge(false);
                rang.Value2 = "Thủ quỹ";
                rang.Font.Bold = true; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rang = _xlWorkSheet.get_Range("f" + rowIndex, "g" + rowIndex);
                rang.Merge(false);
                rang.Value2 = "Kế toán";
                rang.Font.Bold = true; rang.Font.Size = 12;
                rang.HorizontalAlignment = exel.XlHAlign.xlHAlignCenter;
                rang.VerticalAlignment = exel.XlVAlign.xlVAlignCenter;

                rowIndex += 3;
                rang = _xlWorkSheet.get_Range("a1", "g" + rowIndex);



                _xlWorkSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
                _xlWorkSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;

                rang = _xlWorkSheet.get_Range("A" + (rowIndex + 5), "g" + (rowIndex + 5));
                rang.Merge(false);
                rang.Value2 = "'-------------------------------------------------------------------------------------------------------------------------------------------------------------";

                rang = _xlWorkSheet.get_Range("a1", "g" + rowIndex);
                Microsoft.Office.Interop.Excel.Range rang_ = _xlWorkSheet.get_Range("A" + (rowIndex + 7), "A" + (rowIndex + 7));

                rang.Copy(rang_);

                rang = _xlWorkSheet.get_Range("a" + (rowIndex + 7), "a" + (rowIndex + 7));
                rang.RowHeight = 30;
            }
            catch (System.Exception ex)
            { MessageBox.Show("loi export file exle " + ex.Message); }
        }
    }
}
